﻿/* Copyright © 2010 Nolics
Nolics.net and NSQLObjects are trademarks of Nolics.

Microsoft Public License (Ms-PL)

This license governs use of the accompanying software. If you use the software, you accept this license. If you do not accept the license, do not use the software.

1. Definitions
The terms "reproduce," "reproduction," "derivative works," and "distribution" have the same meaning here as under U.S. copyright law.
A "contribution" is the original software, or any additions or changes to the software.
A "contributor" is any person that distributes its contribution under this license.
"Licensed patents" are a contributor's patent claims that read directly on its contribution.

2. Grant of Rights
(A) Copyright Grant- Subject to the terms of this license, including the license conditions and limitations in section 3, each contributor grants you a non-exclusive, worldwide, royalty-free copyright license to reproduce its contribution, prepare derivative works of its contribution, and distribute its contribution or any derivative works that you create.
(B) Patent Grant- Subject to the terms of this license, including the license conditions and limitations in section 3, each contributor grants you a non-exclusive, worldwide, royalty-free license under its licensed patents to make, have made, use, sell, offer for sale, import, and/or otherwise dispose of its contribution in the software or derivative works of the contribution in the software.

3. Conditions and Limitations
(A) No Trademark License- This license does not grant you rights to use any contributors' name, logo, or trademarks.
(B) If you bring a patent claim against any contributor over patents that you claim are infringed by the software, your patent license from such contributor to the software ends automatically.
(C) If you distribute any portion of the software, you must retain all copyright, patent, trademark, and attribution notices that are present in the software.
(D) If you distribute any portion of the software in source code form, you may do so only under this license by including a complete copy of this license with your distribution. If you distribute any portion of the software in compiled or object code form, you may only do so under a license that complies with this license.
(E) The software is licensed "as-is." You bear the risk of using it. The contributors give no express warranties, guarantees or conditions. You may have additional consumer rights under your local laws which this license cannot change. To the extent permitted under your local laws, the contributors exclude the implied warranties of merchantability, fitness for a particular purpose and non-infringement. 
 */


using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;
using System.Linq;
using NSQLObjects.Exceptions;
using NSQLObjects.Filter;
using NSQLObjects.Persistence;

namespace NSQLObjects.Model
{
    public abstract class BaseQuery<T> : List<T>, IQueryFill<T>, IPagedList, ISortableList<T>, IFilterableList<T> where T : IRow, new()
    {

        protected BaseQuery() {
            SortColumns = new List<Tuple<PropertyInfo, bool>>();
            PageSize = 20;
        }

        /// <summary>
        /// Load's one page set from database and optionally count total available rows
        /// </summary>
        /// <param name="context"></param>
        /// <param name="fTotalRows">Count total rows</param>
        public BaseQuery<T> LoadPage(SqlDataContext context, bool fTotalRows = true) {
            if (PageSize == 0) {
                Load(context);
                TotalRows = Count;
            } else {
                LoadRange(context, CurrentPage * PageSize + 1, CurrentPage * PageSize + PageSize);
                if (fTotalRows)
                    QueryCount(context);
            }

            return this;
        }

        /// <summary>
        /// Load single row
        /// </summary>
        /// <param name="fMustFind">Throw error is row is not found</param>
        /// <returns>Only row from query</returns>
        public T LoadSingle(SqlDataContext context, bool fMustFind) {
            Clear();
            Load(context);
            if (Count > 1)
                throw new InvalidDataException("Singleton query returned {0} rows".F(Count));
            if (Count == 0) {
                if (fMustFind)
                    throw new InvalidDataException("Singleton query failed to return any rows");
                return default(T);
            }
            return this[0];
        }

        /// <summary>
        /// Load given range of rows. Note! First and last row are 1 based
        /// </summary>
        /// <param name="context">Data context</param>
        /// <param name="firstRow">First row (1 == first row in query)</param>
        /// <param name="lastRows">Last row</param>
        public BaseQuery<T> LoadRange(SqlDataContext context, int firstRow, int lastRow) {
            string orderBy = null;
            SqlCommand cmd = GetFillQuery(ref orderBy);
            cmd.Parameters.Add("nn_first_row", System.Data.SqlDbType.Int).Value = firstRow;
            cmd.Parameters.Add("nn_last_row", System.Data.SqlDbType.Int).Value = lastRow;
            string rownumOrderBy = orderBy;
            if (rownumOrderBy == null)
                rownumOrderBy = "(select 1)";
            cmd.CommandText = "select t2.* from (select t1.*, ROW_NUMBER() OVER (ORDER BY " +
                rownumOrderBy + ") as nn_row_number from (" + cmd.CommandText +
                ") t1) t2 where t2.nn_row_number >= @nn_first_row and t2.nn_row_number <= @nn_last_row ";
            if (orderBy != null)
                cmd.CommandText += " ORDER BY " + orderBy;
            context.FillQuery(cmd, this);
            return this;
        }

        private SqlCommand GetFillQuery(ref string orderBy) {
            SqlCommand cmd = GeneratedFillQuery();
            if (!GeneratedOrderBy(ref orderBy) && SortColumns.Count > 0) {
                StringBuilder sb = new StringBuilder();
                foreach (var s in SortColumns) {
                    if (sb.Length > 0)
                        sb.Append(", ");
                    sb.Append('[');
                    sb.Append(s.Item1.Name);
                    sb.Append(']');
                    if (s.Item2)
                        sb.Append(" DESC");
                }
                orderBy = sb.ToString();
            }
            ApplyQueryFilter(cmd);
            return cmd;
        }

        /// <summary>
        /// Apply filter to query
        /// </summary>
        /// <param name="cmd"></param>
        protected virtual void ApplyQueryFilter(SqlCommand cmd) {

            StringBuilder sqlWhere = new StringBuilder();
            sqlWhere.Append("SELECT w.* FROM (");
            sqlWhere.Append(cmd.CommandText);
            sqlWhere.Append(") w");
            int filterCount = 1;
            foreach (FilterBase fb in filters) {
                string sqlSegment = fb.GetFilterSQL(cmd, "P" + filterCount.ToString());
                if (sqlSegment == null)
                    continue;
                if (filterCount > 1)
                    sqlWhere.Append(" AND ");
                else
                    sqlWhere.Append(" WHERE ");
                sqlWhere.Append(sqlSegment);
                filterCount++;
            }
            if (filterCount > 1)
                cmd.CommandText = sqlWhere.ToString();
        }

        /// <summary>
        /// Load all rows
        /// </summary>
        /// <param name="context">Query context</param>
        /// <returns>Self</returns>
        public BaseQuery<T> Load(SqlDataContext context) {
            string orderBy = null;
            SqlCommand cmd = GetFillQuery(ref orderBy);
            if (orderBy != null)
                cmd.CommandText += " ORDER BY " + orderBy;
            context.FillQuery(cmd, this);
            return this;
        }


        /// <summary>
        /// Retries count of rows from database (without actually retrieving rows)
        /// </summary>
        /// <param name="context">Query context</param>
        /// <returns></returns>
        public int QueryCount(SqlDataContext context) {
            string tmp = null;
            SqlCommand cmd = GetFillQuery(ref tmp);
            cmd.CommandText = "select count(*) from (" + cmd.CommandText + ") t1";
            TotalRows = context.LoadQueryCount(cmd);
            return TotalRows;
        }

        /// <summary>
        /// Override to prevent sorting
        /// </summary>
        /// <param name="pi">Property to sort on</param>
        /// <returns>True if sorting is allowed</returns>
        protected virtual bool OnCanSortBy(PropertyInfo pi) {
            string tmp = null;
            return !GeneratedOrderBy(ref tmp);
        }


        protected abstract SqlCommand GeneratedFillQuery();

        /// <summary>
        /// Retrieve query order
        /// </summary>
        /// <param name="orderBy">Order by string</param>
        /// <returns>True if order is fixed</returns>
        protected virtual bool GeneratedOrderBy(ref string orderBy) {
            return false;
        }

        protected virtual SqlParameter CreateParameter(SqlCommand forCommand, string parameterName) {
            SqlParameter par = new SqlParameter();
            par.ParameterName = parameterName;
            forCommand.Parameters.Add(par);
            return par;
        }

        #region IQuery Members

        public virtual T AddRow() {
            T tmp = new T();
            Add(tmp);
            return tmp;
        }


        //Type IQueryFill.GetRowType() {
        //    return typeof(T);
        //}

        #endregion

        #region IPagedList Members

        public int PageSize { get; set; }

        public int CurrentPage { get; set; }

        public int TotalRows { get; protected set; }

        #endregion

        #region ISortableList Members

        public bool CanSortBy(PropertyInfo property) {
            return OnCanSortBy(property);
        }

        public void ClearSorting() {
            SortColumns.Clear();
        }

        public void AddSortColumn(PropertyInfo property, bool fDescending) {

            SortColumns.Add(new Tuple<PropertyInfo, bool>(property, fDescending));
        }

        public IEnumerable<Tuple<PropertyInfo, bool>> GetSortColumns() {
            return SortColumns;
        }

        #endregion

        /// <summary>
        /// Current columns used for sorting
        /// </summary>
        public List<Tuple<PropertyInfo, bool>> SortColumns { get; private set; }

        #region IFilterable<T> Members

        public void AddFilter(FilterBase filter) {
            filters.Add(filter);
        }

        public void ClearFilters() {
            filters.Clear();
        }

        public IEnumerable<FilterBase> Filters {
            get {
                return filters;
            }
        }

        public Type FilterRowType {
            get {
                return typeof(T);
            }
        }
        #endregion
        private List<FilterBase> filters = new List<FilterBase>();


    }
}
